package com.hbpa.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.hbpa.dao.StudentRegDao;
import com.hbpa.form.StudentReg;
import com.hbpa.util.ConnectionFactory;
import com.hbpa.util.JdbcUtil;

public class StudentRegDaoImpl implements StudentRegDao {

	public boolean addStudentBaseInfor(StudentReg st) {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = ConnectionFactory.getConnection();
			con.setAutoCommit(false);
			ps = con.prepareStatement("insert into st_jb(" +
					"dd,qdui,xh,jie,xm," +
					"xb,mz,sr,zzmm,jg," +
					"sxzy,sfz,lxdh,email,txdz," +
					"yb,jtdh,qq,hkszd,wyfs," +
					"sg,tz,sl,tc,yd,qdian,jrzz,age) values(?,?,?,?,?," +
					"?,?,?,?,?," +
					"?,?,?,?,?," +
					"?,?,?,?,?," +
					"?,?,?,?,?,?,?,?)");
			 
			ps.setString(1, st.getDd());
			ps.setString(2, st.getQdui());
			ps.setString(3, st.getXh());
			ps.setString(4, st.getJie());
			ps.setString(5, st.getXm());
			
			ps.setString(6, st.getXb());
			ps.setString(7, st.getMz());
			ps.setString(8, st.getSr());
			ps.setString(9, st.getZzmm());
			ps.setString(10, st.getJg());
			
			ps.setString(11, st.getSxzy());
			ps.setString(12, st.getSfz());
			ps.setString(13, st.getLxdh());
			ps.setString(14, st.getEmail());
			ps.setString(15, st.getTxdz());
			
			ps.setString(16, st.getYb());
			ps.setString(17, st.getJtdh());
			ps.setString(18, st.getQq());
			ps.setString(19, st.getHkszd());
			ps.setString(20, st.getWyfs());
			
			ps.setString(21, st.getSg());
			ps.setString(22, st.getTz());
			ps.setString(23, st.getSl());
			ps.setString(24, st.getTc());
			ps.setString(25, st.getYd());
			ps.setString(26, st.getQdidan());
			ps.setString(27, st.getJrzz());
			ps.setString(28, st.getAge());
			
			ps.executeUpdate();
			con.commit();
			return true;
		} catch (SQLException e) {
			if(con!=null){try{con.rollback();}catch(Exception ex){}}
			e.printStackTrace();
		}finally{
			JdbcUtil.close(ps, con);
		}
		return false;
	}

	public boolean checkStudentReg(StudentReg st) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = ConnectionFactory.getConnection(); 
			ps = con.prepareStatement("select count(*) as c from st_jb where xh = ?");
			 
			ps.setString(1, st.getXh());
			
			rs = ps.executeQuery();
			if(rs.next())
			{ 
				String count = rs.getString("c");
				if(count.trim().equals("1")){
					return true;
				}else{
					return false;
				}
			}
			 
		} catch (SQLException e) { 
			e.printStackTrace();
		}finally{
			JdbcUtil.close(rs,ps,con);
		}
		return false;
	}
     
	 
	public boolean checkStudentReg(String xh) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = ConnectionFactory.getConnection(); 
			ps = con.prepareStatement("select count(*) as c from st_jb where xh = ?");
			 
			ps.setString(1, xh);
			rs = ps.executeQuery();
			if(rs.next())
			{ 
				String count = rs.getString("c");
				if(count.trim().equals("1")){
					return true;
				}else{
					return false;
				}
			}
			 
		} catch (SQLException e) { 
			e.printStackTrace();
		}finally{
			JdbcUtil.close(rs,ps,con);
		}
		return false;
	}
	
	public boolean updateStudentReg(StudentReg st) {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = ConnectionFactory.getConnection();
			con.setAutoCommit(false);
			ps = con.prepareStatement("update st_jb set " +
					"dd=?,qdui=?,jie=?,xm=?,xb=?," +
					"mz=?,sr=?,zzmm=?,jg=?,sxzy=?," +
					"sfz=?,lxdh=?,email=?,txdz=?,yb=?," +
					"jtdh=?,qq=?,hkszd=?,wyfs=?,sg=?," +
					"tz=?,sl=?,tc=?,yd=?,qdian=?,jrzz=?,age=?" +
					"where xh=?;");
			 
			ps.setString(1, st.getDd());
			ps.setString(2, st.getQdui());
			ps.setString(3, st.getJie());
			ps.setString(4, st.getXm());
			
			ps.setString(5, st.getXb());
			ps.setString(6, st.getMz());
			ps.setString(7, st.getSr());
			ps.setString(8, st.getZzmm());
			ps.setString(9, st.getJg());
			
			ps.setString(10, st.getSxzy());
			ps.setString(11, st.getSfz());
			ps.setString(12, st.getLxdh());
			ps.setString(13, st.getEmail());
			ps.setString(14, st.getTxdz());
			
			ps.setString(15, st.getYb());
			ps.setString(16, st.getJtdh());
			ps.setString(17, st.getQq());
			ps.setString(18, st.getHkszd());
			ps.setString(19, st.getWyfs());
			
			ps.setString(20, st.getSg());
			ps.setString(21, st.getTz());
			ps.setString(22, st.getSl());
			ps.setString(23, st.getTc());
			ps.setString(24, st.getYd());
			ps.setString(25, st.getQdidan());
			ps.setString(26, st.getJrzz());
			ps.setString(27, st.getAge());
			ps.setString(28, st.getXh());
			
			ps.executeUpdate();
			con.commit();
			return true;
		} catch (SQLException e) {
			if(con!=null){try{con.rollback();}catch(Exception ex){}}
			e.printStackTrace();
		}finally{
			JdbcUtil.close(ps, con);
		}
		return false;
	}

	public StudentReg getStudentRegByXh(String xh) {
		if(checkStudentReg(xh)==false)
		{
			return null;
		}
		
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StudentReg st = new StudentReg();
		try {
			con = ConnectionFactory.getConnection(); 
			ps = con.prepareStatement("select * from st_jb where xh = ?");
			 
			ps.setString(1, xh);
			rs = ps.executeQuery();
			 
			if(rs.next())
			{ 
				 String dd = rs.getString("dd");
				 st.setDd(dd);
				 String qdui = rs.getString("qdui");
				 st.setQdui(qdui);
				 String xuehao = rs.getString("xh");
				 st.setXh(xuehao);
				 String jie = rs.getString("jie");
				 st.setJie(jie);
				 String xm = rs.getString("xm");
				 st.setXm(xm);
				 
				 String xb = rs.getString("xb");
				 st.setXb(xb);
				 String mz = rs.getString("mz");
				 st.setMz(mz);
				 String sr = rs.getString("sr");
				 st.setSr(sr);
				 String zzmm = rs.getString("zzmm");
				 st.setZzmm(zzmm);
				 String jg = rs.getString("jg");
				 st.setJg(jg);
				 
				 String sxzy = rs.getString("sxzy");
				 st.setSxzy(sxzy);
				 String sfz = rs.getString("sfz");
				 st.setSfz(sfz);
				 String lxdh = rs.getString("lxdh");
				 st.setLxdh(lxdh);
				 String email = rs.getString("email");
				 st.setEmail(email);
				 String txdz = rs.getString("txdz");
				 st.setTxdz(txdz);
				 
				 String yb = rs.getString("yb");
				 st.setYb(yb);
				 String jtdh = rs.getString("jtdh");
				 st.setJtdh(jtdh);
				 String qq = rs.getString("qq");
				 st.setQq(qq);
				 String hkszd = rs.getString("hkszd");
				 st.setHkszd(hkszd);
				 String wyfs = rs.getString("wyfs");
				 st.setWyfs(wyfs);
				 
				 String sg = rs.getString("sg");
				 st.setSg(sg);
				 String tz = rs.getString("tz");
				 st.setTz(tz);
				 String sl = rs.getString("sl");
				 st.setSl(sl);
				 String tc = rs.getString("tc");
				 st.setTc(tc);
				 String yd = rs.getString("yd");
				 st.setYd(yd);
				 String qdian = rs.getString("qdian");
				 st.setQdidan(qdian);
				 String jrzz = rs.getString("jrzz");
				 st.setJrzz(jrzz);
				 
				 String age = rs.getString("age");
				 st.setAge(age);
			}
			 return st;
		} catch (SQLException e) { 
			e.printStackTrace();
		}finally{
			JdbcUtil.close(rs,ps,con);
		} 
		return null;
	}

	public boolean isShenHeByXh(String xh) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StudentReg st = new StudentReg();
		try {
			con = ConnectionFactory.getConnection(); 
			ps = con.prepareStatement("select xg from login_acc where xh = ?");
			 
			ps.setString(1, xh);
			rs = ps.executeQuery();
			 
			if(rs.next())
			{ 
				 String xg = rs.getString("xg");
				 if(xg.equals("1"))
				 {
					 return true;
			     }else{
			    	 return false;
			     }
			}
			
		} catch (SQLException e) { 
			e.printStackTrace();
		}finally{
			JdbcUtil.close(rs,ps,con);
		} 
		return false;
	}

	public boolean shenHeByXh(String xh) {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = ConnectionFactory.getConnection();
			con.setAutoCommit(false);
			ps = con.prepareStatement("update login_acc set xg='1' where xh = ?;");
			 
			ps.setString(1, xh);
			ps.executeUpdate();
			con.commit();
			return true;
		} catch (SQLException e) {
			if(con!=null){try{con.rollback();}catch(Exception ex){}}
			e.printStackTrace();
		}finally{
			JdbcUtil.close(ps, con);
		}
		return false;
	}

	public boolean quexiaoShenHeByXh(String xh) {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = ConnectionFactory.getConnection();
			con.setAutoCommit(false);
			ps = con.prepareStatement("update login_acc set xg='0' where xh = ?;");
			 
			ps.setString(1, xh);
			ps.executeUpdate();
			con.commit();
			return true;
		} catch (SQLException e) {
			if(con!=null){try{con.rollback();}catch(Exception ex){}}
			e.printStackTrace();
		}finally{
			JdbcUtil.close(ps, con);
		}
		return false;
	}

	public List<StudentReg> getStudentRegByXm(String xming) {
		Connection con = null;
		Statement sta = null;
		ResultSet rs = null;
		List<StudentReg> list = new ArrayList<StudentReg>();
		StudentReg st = null;
		try {
			con = ConnectionFactory.getConnection(); 
			sta = con.createStatement();
			String sql = "select * from st_jb where xm like '%"+xming+"%'";
			 
			rs = sta.executeQuery(sql);
			 
			while(rs.next())
			{ 
				 st = new StudentReg();
				 String dd = rs.getString("dd");
				 st.setDd(dd);
				 String qdui = rs.getString("qdui");
				 st.setQdui(qdui);
				 String xuehao = rs.getString("xh");
				 st.setXh(xuehao);
				 String jie = rs.getString("jie");
				 st.setJie(jie);
				 String xm = rs.getString("xm");
				 st.setXm(xm);
				 
				 String xb = rs.getString("xb");
				 st.setXb(xb);
				 String mz = rs.getString("mz");
				 st.setMz(mz);
				 String sr = rs.getString("sr");
				 st.setSr(sr);
				 String zzmm = rs.getString("zzmm");
				 st.setZzmm(zzmm);
				 String jg = rs.getString("jg");
				 st.setJg(jg);
				 
				 String sxzy = rs.getString("sxzy");
				 st.setSxzy(sxzy);
				 String sfz = rs.getString("sfz");
				 st.setSfz(sfz);
				 String lxdh = rs.getString("lxdh");
				 st.setLxdh(lxdh);
				 String email = rs.getString("email");
				 st.setEmail(email);
				 String txdz = rs.getString("txdz");
				 st.setTxdz(txdz);
				 
				 String yb = rs.getString("yb");
				 st.setYb(yb);
				 String jtdh = rs.getString("jtdh");
				 st.setJtdh(jtdh);
				 String qq = rs.getString("qq");
				 st.setQq(qq);
				 String hkszd = rs.getString("hkszd");
				 st.setHkszd(hkszd);
				 String wyfs = rs.getString("wyfs");
				 st.setWyfs(wyfs);
				 
				 String sg = rs.getString("sg");
				 st.setSg(sg);
				 String tz = rs.getString("tz");
				 st.setTz(tz);
				 String sl = rs.getString("sl");
				 st.setSl(sl);
				 String tc = rs.getString("tc");
				 st.setTc(tc);
				 String yd = rs.getString("yd");
				 st.setYd(yd);
				 String qdian = rs.getString("qdian");
				 st.setQdidan(qdian);
				 String jrzz = rs.getString("jrzz");
				 st.setJrzz(jrzz);
				 
				 String age = rs.getString("age");
				 st.setAge(age);
				 list.add(st);
			}
			 return list;
		} catch (SQLException e) { 
			e.printStackTrace();
		}finally{
			JdbcUtil.close(rs,sta,con);
		} 
		return null;
	}

	public boolean delStudentReg(String xh) {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = ConnectionFactory.getConnection();
			con.setAutoCommit(false);
			ps = con.prepareStatement("delete from st_jb where xh = ?;");
			 
			ps.setString(1, xh);
			ps.executeUpdate();
			con.commit();
			return true;
		} catch (SQLException e) {
			if(con!=null){try{con.rollback();}catch(Exception ex){}}
			e.printStackTrace();
		}finally{
			JdbcUtil.close(ps, con);
		}
		return false;
	}

}
